//
//  DatabaseHandle.m
//  礼物说
//
//  Created by dllo on 16/4/25.
//  Copyright © 2016年 sunzhichao. All rights reserved.
//

#import "DatabaseHandle.h"
#import "FMDatabase.h"
#import "FMDatabaseQueue.h"

@interface DatabaseHandle ()

@property (nonatomic, retain) FMDatabase *FMDB;
@property (nonatomic, copy) NSString *filePath;

@end

@implementation DatabaseHandle

- (void)dealloc{
    [_FMDB release];
    [super dealloc];
}

+ (instancetype)shareDataBase
{
    static DatabaseHandle *dataBase = nil;
    if (dataBase == nil) {
        dataBase = [[DatabaseHandle alloc] init];
    }
    return dataBase;
}

#pragma mark - 打开
- (void)openDB{
    
    /** 绝对路径【YES / NO】*/
    NSString *file = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)lastObject];
    self.filePath = [file stringByAppendingPathComponent:@"dataBaseFMDB.db"];
    
    NSLog(@"%@", _filePath);
    
    //FMDB进行了OC的封装
    //注意先创建对象
    self.FMDB = [[FMDatabase alloc] initWithPath:_filePath];
    
    BOOL ret = [self.FMDB open];
    if (ret) {
        NSLog(@"打开成功");
    }else{
        
        NSLog(@"打开失败");
    }
}
#pragma mark - 关闭
- (void)closeDB{
    
    BOOL ret = [self.FMDB close];
    if (ret) {
        NSLog(@"关闭成功");
    }else{
        NSLog(@"关闭失败");
    }
}

#pragma mark - 创建表单
- (void)createTable{
    
    /** 如果不存在创建表*/
    NSString *sqlStr = @"CREATE TABLE IF NOT EXISTS liwu(number integer PRIMARY KEY AUTOINCREMENT, name TEXT, url TEXT, picture TEXT, price TEXT)";
    
    BOOL ret = [self.FMDB executeUpdate:sqlStr];
    if (ret) {
        NSLog(@"表单创建成功");
        
    }else{
        NSLog(@"表单创建失败");
    }
}
#pragma mark - 创建GongLv表单
- (void)createTableGongLv{
    
    NSString *sqlStr = @"CREATE TABLE IF NOT EXISTS gonglv(number integer PRIMARY KEY AUTOINCREMENT, name TEXT, url TEXT, picture TEXT, like TEXT)";
    
    BOOL ret = [self.FMDB executeUpdate:sqlStr];
    if (ret) {
        NSLog(@"表单创建成功");
        
    }else{
        NSLog(@"表单创建失败");
    }
}

#pragma mark - 删除表单
- (void)deleteTable{
    
    NSString *sqlStr = @"drop table liwu";
    BOOL ret = [self.FMDB executeUpdate:sqlStr];
    if (ret) {
        NSLog(@"删除表单");
    }else{
        
        NSLog(@"删除表单");
    }
}
#pragma mark - 删除Gonglv表单
- (void)deleteTableGonglv{
    
    NSString *sqlStr = @"drop table gongLv";
    BOOL ret = [self.FMDB executeUpdate:sqlStr];
    if (ret) {
        NSLog(@"删除表单");
    }else{
        
        NSLog(@"删除表单");
    }
}

- (void)insertDataWithLiWu:(LiWu *)liwu{
    
    NSString *sqlStr = [NSString stringWithFormat:@"insert into liwu(name, url, picture , price) values ('%@', '%@', '%@', '%@')", liwu.name , liwu.url, liwu.pictrue, liwu.price];
    
    BOOL ret = [self.FMDB executeUpdate:sqlStr];
    if (ret) {
        NSLog(@"插入数据成功");
    }else{
        
        NSLog(@"插入数据失败");
    }
}
- (void)insertDataWithGongLv:(gongLv *)gongLv{
    
    NSString *sqlStr = [NSString stringWithFormat:@"insert into gonglv(name, url, picture , like) values ('%@', '%@', '%@', '%@')", gongLv.name , gongLv.url, gongLv.picture, gongLv.like];
    
    BOOL ret = [self.FMDB executeUpdate:sqlStr];
    if (ret) {
        NSLog(@"插入数据成功");
    }else{
        
        NSLog(@"插入数据失败");
    }
    
}

- (void)deleteDataWithLiWu:(LiWu *)liwu Name:(NSString *)name{
    
    
    NSString *sqlStr = [NSString stringWithFormat:@"delete from liwu where name = '%@'", name];
    
    BOOL ret = [self.FMDB executeUpdate:sqlStr];
    if (ret) {
        NSLog(@"删除数据成功");
    }else{
        
        NSLog(@"删除数据失败");
    }
    
}
- (void)deleteDataWithGongLV:(gongLv *)gonglv Name:(NSString *)name{
    
    
    NSString *sqlStr = [NSString stringWithFormat:@"delete from gonglv where name = '%@'", name];
    
    BOOL ret = [self.FMDB executeUpdate:sqlStr];
    if (ret) {
        NSLog(@"删除数据成功");
    }else{
        
        NSLog(@"删除数据失败");
    }
    
    
}

- (NSMutableArray *)selectTable {
    
    NSString *sql = [NSString stringWithFormat:@"select * from liwu"];
    //FMResultSet类用来查询
    FMResultSet *resSet = [self.FMDB executeQuery:sql];
    NSMutableArray *stuArr = [NSMutableArray array];
    
    while ([resSet next]) {
        // 取出每一个key值
        NSString *name = [resSet stringForColumn:@"name"];
        NSString *url = [resSet stringForColumn:@"url"];
        NSString *picture = [resSet stringForColumn:@"picture"];
        NSString *price = [resSet stringForColumn:@"price"];
        
        // 创建model 给model 赋值
        LiWu * model = [[LiWu alloc] init];
        model.name = name;
        model.url = url;
        model.pictrue = picture;
        model.price = price;
        [stuArr addObject: model];
        [model release];
    }

    return stuArr;
}

- (NSMutableArray *)selectTableGongLv{
    
    NSString *sql = [NSString stringWithFormat:@"select * from gonglv"];
    //FMResultSet类用来查询
    FMResultSet *resSet = [self.FMDB executeQuery:sql];
    NSMutableArray *stuArr = [NSMutableArray array];
    
    while ([resSet next]) {
        // 取出每一个key值
        NSString *name = [resSet stringForColumn:@"name"];
        NSString *url = [resSet stringForColumn:@"url"];
        NSString *picture = [resSet stringForColumn:@"picture"];
        NSString *like = [resSet stringForColumn:@"like"];
        
        // 创建model 给model 赋值
        gongLv * model = [[gongLv alloc] init];
        model.name = name;
        model.url = url;
        model.picture = picture;
        model.like = like;
        [stuArr addObject: model];
        [model release];
    }
    return stuArr;
}

- (NSMutableArray *)selectDataWithName:(NSString *)name {

    NSString *sql = [NSString stringWithFormat:@"select * from liwu where name like '%%%@%%'",name];
    //FMResultSet类用来查询
    FMResultSet *resSet = [self.FMDB executeQuery:sql];
    NSMutableArray *stuArr = [NSMutableArray array];
    
    while ([resSet next]) {
        LiWu * liwu = [[LiWu alloc] init];
        liwu.name = [resSet stringForColumn:@"name"];
        liwu.url = [resSet stringForColumn:@"url"];
        liwu.pictrue = [resSet stringForColumn:@"picture"];
        liwu.price = [resSet stringForColumn:@"price"];
        
        [stuArr addObject:liwu];
        [liwu release];
    }
    return stuArr;
}

- (NSMutableArray *)selectDataGongLvWithName:(NSString *)name{
    
    NSString *sql = [NSString stringWithFormat:@"select * from gonglv where name like '%%%@%%'",name];
    //FMResultSet类用来查询
    FMResultSet *resSet = [self.FMDB executeQuery:sql];
    NSMutableArray *stuArr = [NSMutableArray array];
    
    while ([resSet next]) {
        gongLv * gonglv = [[gongLv alloc] init];
        gonglv.name = [resSet stringForColumn:@"name"];
        gonglv.url = [resSet stringForColumn:@"url"];
        gonglv.picture = [resSet stringForColumn:@"picture"];
        gonglv.like = [resSet stringForColumn:@"like"];
        [stuArr addObject:gonglv];
        [gonglv release];
    }
    return stuArr;
    
}




@end
